<?php
	class vanbandidb
	{
		public function Add($SovaKyHieu, $TrichYeu, $MaLoaiVanBan,$NgayBanHanh,$NgayHetHan, $MaTrangThai, $NoiBanHanh, $MaNguoiDangKy, $MaPhongBan, $DonViNgoai, $MaNguoiDung)
		{
			$sql = " INSERT INTO `tbl_vanbandi` (`SovaKyHieu`, `TrichYeu`, `MaLoaiVanBan`, `MaTrangThai`, `NgayBanHanh`, `NgayHetHan`, `NgayDangKy`,`NoiBanHanh`, `MaNguoiDangKy`, `MaPhongBan`, `DonViNgoai`, `MaNguoiDung`) VALUES ('$SovaKyHieu', '$TrichYeu', '$MaLoaiVanBan' , '$MaTrangThai', STR_TO_DATE('$NgayBanHanh','%d,%m,%Y') , STR_TO_DATE('$NgayHetHan','%d,%m,%Y'),CURDATE(),'$NoiBanHanh', '$MaNguoiDangKy', '$MaPhongBan', '$DonViNgoai', '$MaNguoiDung') ";
			$data = new dataservice();
			return $data->ExecuteNonQuery($sql);
		}
		
		public function Delete($MaVanBanDi)
		{
			$sql = "DELETE FROM `tbl_vanbandi` WHERE MaVanBanDi = '$MaVanBanDi' ";
			$data = new dataservice();
			return $data->ExecuteNonQuery($sql);
		}
		
		public function Update($MaVanBanDi, $SovaKyHieu, $TrichYeu, $MaLoaiVanBan,$NgayBanHanh,$NgayHetHan, $NoiBanHanh,  $MaPhongBan,  $DonViNgoai)
		{
			$sql = " UPDATE `tbl_vanbandi` SET 
			`SovaKyHieu` = '$SovaKyHieu',
			`TrichYeu` = '$TrichYeu',
			`MaLoaiVanBan`= $MaLoaiVanBan,
			`NgayBanHanh`= STR_TO_DATE('$NgayBanHanh','%d,%m,%Y') ,
			`NgayHetHan`= STR_TO_DATE('$NgayHetHan','%d,%m,%Y'),
			`NoiBanHanh`= '$NoiBanHanh' ";
			if($MaPhongBan != 0)
			{
				$sql .= " ,`MaPhongBan`= '$MaPhongBan' ";
			}
			elseif($DonViNgoai != "")
			{
				$sql .= " ,`DonViNgoai` = '$DonViNgoai' ";
			}
			$sql .= "WHERE `MaVanBanDi` = '$MaVanBanDi' ";
		
			$data = new dataservice();
			return $data->ExecuteNonQuery($sql);
		}
		
		public function GetList($MaVanBanDi, $SovaKyHieu, $NgayBanHanh, $NgayHetHan, $TrichYeu, $MaLoaiVanBan, $MaTrangThai, $NgayDen, $NoiBanHanh, $MaNguoiDangKy, $MaPhongBan, $DonViNgoai)
		{
			$sql = " SELECT * FROM `tbl_vanbandi` vb, `tbl_loaivanban` lvb, `tbl_trangthai` tt WHERE  vb.MaLoaiVanBan = lvb.MaLoaiVanBan AND vb.MaTrangThai = tt.MaTrangThai ";
			if($MaVanBanDi != "")
				$sql .= " AND vb.MaVanBanDi = '$MaVanBanDi' ";
			if($SovaKyHieu != "")
				$sql .= " AND vb.SovaKyHieu LIKE '%$SovaKyHieu%'";
			if($NgayBanHanh != "")
				$sql .= " AND vb.NgayBanHanh = STR_TO_DATE('$NgayBanHanh','%d,%m,%Y')";
			if($NgayHetHan != "")
				$sql .= " AND vb.NgayHetHan = STR_TO_DATE('$NgayHetHan','%d,%m,%Y')";
			if($TrichYeu != "")
				$sql .= " AND vb.TrichYeu LIKE '%$TrichYeu%'";
			if($MaLoaiVanBan != "")
				$sql .= " AND vb.MaLoaiVanBan = $MaLoaiVanBan";
			if($MaTrangThai != "")
				$sql .= " AND vb.MaTrangThai = '$MaTrangThai' ";
			if($NgayDen != "")
				$sql .= " AND vb.NgayDen = STR_TO_DATE('$NgayDen','%d,%m,%Y')";
			
			if($NoiBanHanh != "")
				$sql .= " AND vb.NoiBanHanh LIKE '%$NoiBanHanh%'";
			
			if($MaNguoiDangKy != "")
				$sql .= " AND vb.MaNguoiDangKy = '$NoiBanHanh'";	
			
			if($MaPhongBan != "")
				$sql .= " AND vb.MaPhongBan = '$MaPhongBan'";	
			
			if($DonViNgoai != "")
				$sql .= " AND vb.DonViNgoai = '$DonViNgoai'";		
			
			$sql .= "ORDER BY vb.MaVanBanDi DESC" ;
			
			$data = new dataservice();
			return $data->ExecuteQuery($sql);
		}
		
		public function PageList($MaVanBanDi, $SovaKyHieu, $NgayBanHanh, $NgayHetHan, $TrichYeu, $MaLoaiVanBan, $MaTrangThai, $NgayDen, $NoiBanHanh, $MaNguoiDangKy, $MaPhongBan, $DonViNgoai, $start, $limit )
		{
			$sql = " SELECT * FROM `tbl_vanbandi` vb, `tbl_loaivanban` lvb, `tbl_trangthai` tt WHERE  vb.MaLoaiVanBan = lvb.MaLoaiVanBan AND vb.MaTrangThai = tt.MaTrangThai ";
			if($MaVanBanDi != "")
				$sql .= " AND vb.MaVanBanDi = '$MaVanBanDi' ";
			if($SovaKyHieu != "")
				$sql .= " AND vb.SovaKyHieu LIKE '%$SovaKyHieu%'";
			if($NgayBanHanh != "")
				$sql .= " AND vb.NgayBanHanh = STR_TO_DATE('$NgayBanHanh','%d,%m,%Y')";
			if($NgayHetHan != "")
				$sql .= " AND vb.NgayHetHan = STR_TO_DATE('$NgayHetHan','%d,%m,%Y')";
			if($TrichYeu != "")
				$sql .= " AND vb.TrichYeu LIKE '%$TrichYeu%'";
			if($MaLoaiVanBan != "")
				$sql .= " AND vb.MaLoaiVanBan = $MaLoaiVanBan";
			if($MaTrangThai != "")
				$sql .= " AND vb.MaTrangThai = '$MaTrangThai' ";
			if($NgayDen != "")
				$sql .= " AND vb.NgayDen = STR_TO_DATE('$NgayDen','%d,%m,%Y')";
			
			if($NoiBanHanh != "")
				$sql .= " AND vb.NoiBanHanh LIKE '%$NoiBanHanh%'";
			
			if($MaNguoiDangKy != "")
				$sql .= " AND vb.MaNguoiDangKy = '$NoiBanHanh'";	
			
			if($MaPhongBan != "")
				$sql .= " AND vb.MaPhongBan = '$MaPhongBan'";	
			
			if($DonViNgoai != "")
				$sql .= " AND vb.DonViNgoai = '$DonViNgoai'";		
			
			$sql .= "  ORDER BY vb.MaVanBanDi DESC LIMIT $start, $limit ";
			
			$data = new dataservice();
			return $data->ExecuteQuery($sql);
		}
		
		public function GetTrangThaiVanBan($MaTrangThai, $MaNguoiDuyet, $SovaKyHieu, $NgayBanHanh, $NgayHetHan,  $NoiBanHanh)
		{
			
			$sql = " SELECT * FROM `tbl_vanbandi` vb, `tbl_loaivanban` lvb, `tbl_trangthai` tt WHERE  vb.MaLoaiVanBan = lvb.MaLoaiVanBan AND vb.MaTrangThai = tt.MaTrangThai ";

			if($MaTrangThai != "")
			$sql .= " AND vb.MaTrangThai = '$MaTrangThai' ";
			if($MaNguoiDuyet != "")
			$sql .= " AND vb.MaNguoiDuyet = '$MaNguoiDuyet' ";
			if($SovaKyHieu != "")
				$sql .= " AND vb.SovaKyHieu LIKE '%$SovaKyHieu%'";
			if($NgayBanHanh != "")
				$sql .= " AND vb.NgayBanHanh = STR_TO_DATE('$NgayBanHanh','%d,%m,%Y')";
			if($NgayHetHan != "")
				$sql .= " AND vb.NgayHetHan = STR_TO_DATE('$NgayHetHan','%d,%m,%Y')";
			if($NoiBanHanh != "")
				$sql .= " AND vb.NoiBanHanh LIKE '%$NoiBanHanh%'";			
			$data = new dataservice();
			return $data->ExecuteQuery($sql);
		}
		
		public function PageTrangThaiVanBan($MaTrangThai, $MaNguoiDuyet, $SovaKyHieu, $NgayBanHanh, $NgayHetHan,
		  $NoiBanHanh, $start, $limit)
		{
			
			$sql = " SELECT * FROM `tbl_vanbandi` vb, `tbl_loaivanban` lvb, `tbl_trangthai` tt WHERE  vb.MaLoaiVanBan = lvb.MaLoaiVanBan AND vb.MaTrangThai = tt.MaTrangThai ";

			if($MaTrangThai != "")
			$sql .= " AND vb.MaTrangThai = '$MaTrangThai' ";
			if($MaNguoiDuyet != "")
			$sql .= " AND vb.MaNguoiDuyet = '$MaNguoiDuyet' ";
			if($SovaKyHieu != "")
				$sql .= " AND vb.SovaKyHieu LIKE '%$SovaKyHieu%'";
			if($NgayBanHanh != "")
				$sql .= " AND vb.NgayBanHanh = STR_TO_DATE('$NgayBanHanh','%d,%m,%Y')";
			if($NgayHetHan != "")
				$sql .= " AND vb.NgayHetHan = STR_TO_DATE('$NgayHetHan','%d,%m,%Y')";
			if($NoiBanHanh != "")
				$sql .= " AND vb.NoiBanHanh LIKE '%$NoiBanHanh%'";	
			$sql .= "  ORDER BY vb.MaVanBanDi DESC LIMIT $start, $limit ";		
			$data = new dataservice();
			return $data->ExecuteQuery($sql);
		}
		
		
		public function ChangeTrangThai($MaVanBanDi, $MaNguoiDuyet, $MaTrangThai)
		{
			$sql = " UPDATE `tbl_vanbandi` SET 
			`MaTrangThai` = $MaTrangThai ";
			
			if($MaNguoiDuyet != "")
			
			$sql .= " ,`MaNguoiDuyet` = $MaNguoiDuyet,  ThoiGianDuyet = CURDATE() ";
			
			$sql .= " WHERE `MaVanBanDi`= '$MaVanBanDi' ";

			$data = new dataservice();
			return $data->ExecuteNonQuery($sql);
		}
		
		
		public function getVanBanDenNguoiDung($MaNguoiDung, $HienTai,  $SovaKyHieu, $NgayBanHanh, $NgayHetHan,  $NoiBanHanh)
		{
			$sql = " SELECT * FROM `tbl_vanbandi` vb, `tbl_loaivanban` lvb, `tbl_trangthai` tt,`tbl_nguoidung` ng  WHERE  vb.MaLoaiVanBan = lvb.MaLoaiVanBan AND vb.MaTrangThai = tt.MaTrangThai AND vb.MaTrangThai = 6 AND ng.MaNguoiDung = vb.MaNguoiDung AND vb.MaNguoiDung = '$MaNguoiDung' ";
			
			if($HienTai == 1)
			$sql .= "  AND ThoiGianDuyet = CURDATE() ";
			if($SovaKyHieu != "")
				$sql .= " AND vb.SovaKyHieu LIKE '%$SovaKyHieu%'";
			if($NgayBanHanh != "")
				$sql .= " AND vb.NgayBanHanh = STR_TO_DATE('$NgayBanHanh','%d,%m,%Y')";
			if($NgayHetHan != "")
				$sql .= " AND vb.NgayHetHan = STR_TO_DATE('$NgayHetHan','%d,%m,%Y')";
			if($NoiBanHanh != "")
				$sql .= " AND vb.NoiBanHanh LIKE '%$NoiBanHanh%'";
			
			$data = new dataservice();
			return $data->ExecuteQuery($sql);

		}
		
		public function pageVanBanDenNguoiDung($MaNguoiDung, $HienTai,  $SovaKyHieu, $NgayBanHanh, $NgayHetHan,  $NoiBanHanh, $start, $limit)
		{
			$sql = " SELECT * FROM `tbl_vanbandi` vb, `tbl_loaivanban` lvb, `tbl_trangthai` tt,`tbl_nguoidung` ng  WHERE  vb.MaLoaiVanBan = lvb.MaLoaiVanBan AND vb.MaTrangThai = tt.MaTrangThai AND vb.MaTrangThai = 6 AND ng.MaNguoiDung = vb.MaNguoiDung AND vb.MaNguoiDung = '$MaNguoiDung' ";
			
			if($HienTai == 1)
			$sql .= "  AND ThoiGianDuyet = CURDATE() ";
			if($SovaKyHieu != "")
				$sql .= " AND vb.SovaKyHieu LIKE '%$SovaKyHieu%'";
			if($NgayBanHanh != "")
				$sql .= " AND vb.NgayBanHanh = STR_TO_DATE('$NgayBanHanh','%d,%m,%Y')";
			if($NgayHetHan != "")
				$sql .= " AND vb.NgayHetHan = STR_TO_DATE('$NgayHetHan','%d,%m,%Y')";
			if($NoiBanHanh != "")
				$sql .= " AND vb.NoiBanHanh LIKE '%$NoiBanHanh%'";
			$sql .= "  ORDER BY vb.MaVanBanDi DESC LIMIT $start, $limit ";
			$data = new dataservice();
			return $data->ExecuteQuery($sql);

		}
		
		public function getVanBanDenNguoiDangKy($MaNguoiDangKy, $HienTai, $SovaKyHieu, $NgayBanHanh, $NgayHetHan, $NoiBanHanh)
		{
			$sql = " SELECT * FROM `tbl_vanbandi` vb, `tbl_loaivanban` lvb, `tbl_trangthai` tt,`tbl_nguoidung` ng  WHERE  vb.MaLoaiVanBan = lvb.MaLoaiVanBan AND vb.MaTrangThai = tt.MaTrangThai AND ng.MaNguoiDung = vb.MaNguoiDangKy AND vb.MaNguoiDangKy = '$MaNguoiDangKy' ";
			
			if($HienTai == 1)
				$sql .= " AND NgayDangKy = CURDATE() ";
			if($SovaKyHieu != "")
				$sql .= " AND vb.SovaKyHieu LIKE '%$SovaKyHieu%'";
			if($NgayBanHanh != "")
				$sql .= " AND vb.NgayBanHanh = STR_TO_DATE('$NgayBanHanh','%d,%m,%Y')";
			if($NgayHetHan != "")
				$sql .= " AND vb.NgayHetHan = STR_TO_DATE('$NgayHetHan','%d,%m,%Y')";
			if($NoiBanHanh != "")
				$sql .= " AND vb.NoiBanHanh LIKE '%$NoiBanHanh%'";
			
			$data = new dataservice();
			return $data->ExecuteQuery($sql);

		}
		
		public function pageVanBanDenNguoiDangKy($MaNguoiDangKy, $HienTai, $SovaKyHieu, $NgayBanHanh, $NgayHetHan, $NoiBanHanh, $start, $limit)
		{
			$sql = " SELECT * FROM `tbl_vanbandi` vb, `tbl_loaivanban` lvb, `tbl_trangthai` tt,`tbl_nguoidung` ng  WHERE  vb.MaLoaiVanBan = lvb.MaLoaiVanBan AND vb.MaTrangThai = tt.MaTrangThai AND ng.MaNguoiDung = vb.MaNguoiDangKy AND vb.MaNguoiDangKy = '$MaNguoiDangKy' ";
			if($HienTai == 1)
				$sql .= " AND NgayDangKy = CURDATE() ";
			if($SovaKyHieu != "")
				$sql .= " AND vb.SovaKyHieu LIKE '%$SovaKyHieu%'";
			if($NgayBanHanh != "")
				$sql .= " AND vb.NgayBanHanh = STR_TO_DATE('$NgayBanHanh','%d,%m,%Y')";
			if($NgayHetHan != "")
				$sql .= " AND vb.NgayHetHan = STR_TO_DATE('$NgayHetHan','%d,%m,%Y')";
			if($NoiBanHanh != "")
				$sql .= " AND vb.NoiBanHanh LIKE '%$NoiBanHanh%'";
			$sql .= "  ORDER BY vb.MaVanBanDi DESC LIMIT $start, $limit ";
			$data = new dataservice();
			return $data->ExecuteQuery($sql);

		}
		
		public function getVanBanDenPhongBan($MaPhongBan,$HienTai,$SovaKyHieu, $NgayBanHanh, $NgayHetHan,  $NoiBanHanh)
		{
			$sql = " SELECT * FROM `tbl_vanbandi` vb, `tbl_loaivanban` lvb, `tbl_trangthai` tt,`tbl_phongban` pb  WHERE  vb.MaLoaiVanBan = lvb.MaLoaiVanBan AND vb.MaTrangThai = tt.MaTrangThai AND vb.MaTrangThai = 6 AND pb.MaPhongBan = vb.MaPhongBan AND vb.MaPhongBan = '$MaPhongBan' ";
			
			if($HienTai == 1)
			$sql .= " AND ThoiGianDuyet = CURDATE() ";
			if($SovaKyHieu != "")
				$sql .= " AND vb.SovaKyHieu LIKE '%$SovaKyHieu%'";
			if($NgayBanHanh != "")
				$sql .= " AND vb.NgayBanHanh = STR_TO_DATE('$NgayBanHanh','%d,%m,%Y')";
			if($NgayHetHan != "")
				$sql .= " AND vb.NgayHetHan = STR_TO_DATE('$NgayHetHan','%d,%m,%Y')";
			if($NoiBanHanh != "")
				$sql .= " AND vb.NoiBanHanh LIKE '%$NoiBanHanh%' ";
			
			$data = new dataservice();
			return $data->ExecuteQuery($sql);

		}
		public function pageVanBanDenPhongBan($MaPhongBan,$HienTai,$SovaKyHieu, $NgayBanHanh, $NgayHetHan,  $NoiBanHanh,  $start, $limit)
		{
			$sql = " SELECT * FROM `tbl_vanbandi` vb, `tbl_loaivanban` lvb, `tbl_trangthai` tt,`tbl_phongban` pb  WHERE  vb.MaLoaiVanBan = lvb.MaLoaiVanBan AND vb.MaTrangThai = tt.MaTrangThai AND vb.MaTrangThai = 6 AND pb.MaPhongBan = vb.MaPhongBan AND vb.MaPhongBan = '$MaPhongBan' ";
			
			if($HienTai == 1)
			$sql .= " AND ThoiGianDuyet = CURDATE() ";
			if($SovaKyHieu != "")
				$sql .= " AND vb.SovaKyHieu LIKE '%$SovaKyHieu%'";
			if($NgayBanHanh != "")
				$sql .= " AND vb.NgayBanHanh = STR_TO_DATE('$NgayBanHanh','%d,%m,%Y')";
			if($NgayHetHan != "")
				$sql .= " AND vb.NgayHetHan = STR_TO_DATE('$NgayHetHan','%d,%m,%Y')";
			if($NoiBanHanh != "")
				$sql .= " AND vb.NoiBanHanh LIKE '%$NoiBanHanh%' ";
				
			$sql .= "  ORDER BY vb.MaVanBanDi DESC LIMIT $start, $limit ";
			
			$data = new dataservice();
			return $data->ExecuteQuery($sql);

		}
		
		public function getVanBanDenCongTy($HienTai,$SovaKyHieu, $NgayBanHanh, $NgayHetHan,  $NoiBanHanh)
		{
			$sql = " SELECT * FROM `tbl_vanbandi` vb, `tbl_loaivanban` lvb, `tbl_trangthai` tt WHERE  vb.MaLoaiVanBan = lvb.MaLoaiVanBan AND vb.MaTrangThai = tt.MaTrangThai AND vb.MaTrangThai = 6  AND vb.MaPhongBan = 0 AND vb.DonViNgoai='' AND vb.MaNguoiDung = 0  ";
			
			if($HienTai == 1)
			$sql .= " AND ThoiGianDuyet = CURDATE() ";
			if($SovaKyHieu != "")
				$sql .= " AND vb.SovaKyHieu LIKE '%$SovaKyHieu%'";
			if($NgayBanHanh != "")
				$sql .= " AND vb.NgayBanHanh = STR_TO_DATE('$NgayBanHanh','%d,%m,%Y')";
			if($NgayHetHan != "")
				$sql .= " AND vb.NgayHetHan = STR_TO_DATE('$NgayHetHan','%d,%m,%Y')";
			if($NoiBanHanh != "")
				$sql .= " AND vb.NoiBanHanh LIKE '%$NoiBanHanh%' ";
			
			$data = new dataservice();
			return $data->ExecuteQuery($sql);

		}
		public function pageVanBanDenCongTy($HienTai,$SovaKyHieu, $NgayBanHanh, $NgayHetHan,  $NoiBanHanh,  $start, $limit)
		{
			$sql = "SELECT * FROM `tbl_vanbandi` vb, `tbl_loaivanban` lvb, `tbl_trangthai` tt WHERE  vb.MaLoaiVanBan = lvb.MaLoaiVanBan AND vb.MaTrangThai = tt.MaTrangThai AND vb.MaTrangThai = 6  AND vb.MaPhongBan = 0 AND vb.DonViNgoai='' AND vb.MaNguoiDung = 0  ";
			
			if($HienTai == 1)
			$sql .= " AND ThoiGianDuyet = CURDATE() ";
			if($SovaKyHieu != "")
				$sql .= " AND vb.SovaKyHieu LIKE '%$SovaKyHieu%'";
			if($NgayBanHanh != "")
				$sql .= " AND vb.NgayBanHanh = STR_TO_DATE('$NgayBanHanh','%d,%m,%Y')";
			if($NgayHetHan != "")
				$sql .= " AND vb.NgayHetHan = STR_TO_DATE('$NgayHetHan','%d,%m,%Y')";
			if($NoiBanHanh != "")
				$sql .= " AND vb.NoiBanHanh LIKE '%$NoiBanHanh%' ";
				
			$sql .= "  ORDER BY vb.MaVanBanDi DESC LIMIT $start, $limit ";
			
			$data = new dataservice();
			return $data->ExecuteQuery($sql);

		}
	}
?>